﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Globalization;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F5Plan : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        private bool update;
        
        public panel_F5Plan()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            this.update = false;

            LoadData();

            this.F5Plan.DoubleClick += F5Plan_DoubleClick;
            this.F5Plan.PopupMenuShowing += F5Plan_PopupMenuShowing;
            this.f5planContext.ItemClicked += f5planContext_ItemClicked;
        }

        ///////////////////////////////////
        /// Call Edit Form
        ///////////////////////////////////
        private void callEditForm()
        {
            DataRow row = F5Plan.GetFocusedDataRow();
            if (!string.IsNullOrEmpty(row[7].ToString()))
            {
                MessageBox.Show("This plan has been Finished!");
            }
            else
            {
                frm_Edit_F5Plan edit_Form = new frm_Edit_F5Plan();
                edit_Form.planid = Int32.Parse(row[0].ToString());
                if (edit_Form.LoadData())
                {
                    edit_Form.ShowDialog();
                    LoadData();
                }
                else
                {
                    MessageBox.Show("Cannot load data for selected record");
                }
            }
        }

        void F5Plan_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void f5planContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.f5planContext.Hide();
            DataRow row = F5Plan.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {                
                case "importSelection":
                    importDatabase();
                    break;
                case "editSelection":
                    callEditForm();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }
 

        void F5Plan_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.f5planContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT [231_F5Plan].ID, [231_F5Plan].OrderNo, [231_F5Plan].PlanNo, [231_F5Plan].PartNo,"
                + " [231_F5Plan].CommandQuantity, [231_F5Plan].CommandDate, [231_F5Plan].DeadLine, [231_F5Plan].FinishedDate,"
                + " [231_F5Plan].FinishedQuantity, [231_F5Plan].FromSerialNo, [231_F5Plan].ToSerialNo,"
                + " [231_F5Plan].SubOrder1, [231_F5Plan].SubOrder2, [231_F5Plan].SubOrder3, [222_Customer].FullName,"
                + " [231_F5Plan].Remark"
                + " FROM [231_F5Plan] LEFT OUTER JOIN [222_Customer] ON [222_Customer].CustomerID = [231_F5Plan].CustomerID"
                + " ORDER BY [231_F5Plan].OrderNo";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridF5Plan.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(string));
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("CommandQuantity", typeof(int));
                newTable.Columns.Add("CommandDate", typeof(DateTime));
                newTable.Columns.Add("DeadLine", typeof(DateTime));
                newTable.Columns.Add("FinishedDate", typeof(DateTime));
                newTable.Columns.Add("FinishedQuantity", typeof(int));
                newTable.Columns.Add("FromSerialNo", typeof(string));
                newTable.Columns.Add("ToSerialNo", typeof(string));
                newTable.Columns.Add("SubOrder1", typeof(string));
                newTable.Columns.Add("SubOrder2", typeof(string));
                newTable.Columns.Add("SubOrder3", typeof(string));
                newTable.Columns.Add("Customer", typeof(string));
                newTable.Columns.Add("Remark", typeof(string));
                gridF5Plan.DataSource = newTable;
            }
            F5Plan.Columns[0].Caption = "ID";
            F5Plan.Columns[1].Caption = "Order No.";
            F5Plan.Columns[2].Caption = "Plan No.";
            F5Plan.Columns[3].Caption = "Part No.";
            F5Plan.Columns[4].Caption = "Command Quantity";
            F5Plan.Columns[5].Caption = "Command Date";
            F5Plan.Columns[6].Caption = "Dead Line";
            F5Plan.Columns[7].Caption = "Finished Date";
            F5Plan.Columns[8].Caption = "Finished Quantity";
            F5Plan.Columns[9].Caption = "From Serial No.";
            F5Plan.Columns[10].Caption = "To Serial No.";
            F5Plan.Columns[11].Caption = "Sub Order 1";
            F5Plan.Columns[12].Caption = "Sub Order 2";
            F5Plan.Columns[13].Caption = "Sub Order 3";
            F5Plan.Columns[14].Caption = "Customer";
            F5Plan.Columns[15].Caption = "Remark";
            F5Plan.Columns[0].Visible = false;
            F5Plan.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=8";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            
            callEditForm(); 
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Do you want to ignore updating?",
                "Confirm Update", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                this.update = false;
            }
            else
            {
                this.update = true;
            }
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {
            DataSet as400 = new DataSet();
            DataSet f5plan = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable f5plan_db2;
            DataTable f5plan_local;                
            try
            {
                //Get Data From AS400                
                masterConnection.Open();
                sql = "select * from openquery(AS400_MPFLIB, 'SELECT MPOR00.ORODNO AS ORDERNO, NEKO00.KOKENO AS PLANNO,"
                    + " MPOR00.ORHNBN AS PARTNO, MPOR00.ORHTSU AS QUANTITY, MPOR00.ORHTBI AS COMMANDDATE, MPOR80.O8NOK1 AS DEADLINE"
                    + " FROM MPOR00"
                    + " LEFT JOIN MPIT00 ON MPIT00.ITHNBN=MPOR00.ORHNBN"
                    + " LEFT JOIN NEKO00 ON NEKO00.KOODNO = MPOR00.ORODNO"
                    + " LEFT JOIN MPOR80 ON MPOR00.ORODNO = MPOR80.O8ODNO"
                    + " WHERE SUBSTRING(MPIT00.ITFILL,16,4)=''0231'' AND MPOR00.ORHNTP=''M''')";
                cmd = new SqlCommand(sql, masterConnection);
                cmd.CommandTimeout = 10000;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                f5plan_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [231_F5Plan]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(f5plan, "231_F5Plan");
                f5plan_local = f5plan.Tables["231_F5Plan"];
                int insert = 0;
                int update = 0;
                DateTime commandDate = new DateTime();
                DateTime deadline = new DateTime();
                foreach (DataRow as400_row in f5plan_db2.Rows)
                {
                    DataRow[] current_row = f5plan_local.Select("OrderNo='" + as400_row["ORDERNO"].ToString().Trim() + "'");
                    if (current_row.Length > 0)
                    {
                        if (this.update)
                        {
                            foreach (DataRow local_row in current_row)
                            {
                                local_row.BeginEdit();
                                local_row["PlanNo"] = as400_row["PLANNO"].ToString().Trim();
                                local_row["PartNo"] = as400_row["PARTNO"].ToString().Trim();
                                commandDate = DateTime.ParseExact(as400_row["COMMANDDATE"].ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture);
                                local_row["CommandDate"] = commandDate.Date;
                                local_row["CommandQuantity"] = as400_row["QUANTITY"].ToString();
                                deadline = DateTime.ParseExact(as400_row["DEADLINE"].ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture);
                                local_row["DeadLine"] = deadline.Date;                               
                                local_row.EndEdit();
                                update++;
                            }
                        }
                    }
                    else
                    {
                        DataRow new_row = f5plan_local.NewRow();
                        new_row["OrderNo"] = as400_row["ORDERNO"].ToString().Trim();
                        new_row["PlanNo"] = as400_row["PLANNO"].ToString().Trim();
                        new_row["PartNo"] = as400_row["PARTNO"].ToString().Trim();
                        commandDate = DateTime.ParseExact(as400_row["COMMANDDATE"].ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture);
                        new_row["CommandDate"] = commandDate.Date;
                        new_row["CommandQuantity"] = as400_row["QUANTITY"].ToString().Trim();
                        deadline = DateTime.ParseExact(as400_row["DEADLINE"].ToString().Trim(), "yyyyMMdd", CultureInfo.InvariantCulture);
                        new_row["DeadLine"] = deadline.Date;
                        new_row["CustomerID"] = "RRC";
                        f5plan_local.Rows.Add(new_row);
                        insert++;
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(f5plan, "231_F5Plan");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=8";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                LoadData();

                MessageBox.Show("Database has been updated successfull.\r\n "
                    + update + " plan has been updated.\r\n "
                    + insert + " plan has been added.");
                                
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }

        private void buttonAddSubOrder_Click(object sender, EventArgs e)
        {
            importSubOrder();
        }

        private void importSubOrder()
        {            
            //OleDbDataReader excel_dataReader;
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();
            /*
            excel_data.Columns.Add("OrderNo", typeof(string));
            excel_data.Columns.Add("SubOrder1", typeof(string));
            excel_data.Columns.Add("SubOrder2", typeof(string));
            excel_data.Columns.Add("SubOrder3", typeof(string));
            */
            bool have_data = false;

            importFile.Filter = "Excel 97-2003 (.xls)|*.xls|Excel 2007-2010 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("SELECT [OrderNo],[SubOrder1],[SubOrder2],[SubOrder3]"
                        + " FROM [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);                    
                    /*
                    excel_dataReader = cmd.ExecuteReader();
                    while (excel_dataReader.Read())
                    {
                        DataRow excel_row = excel_data.NewRow();
                        excel_row["OrderNo"] = excel_row["OrderNo"].ToString().Trim();
                        excel_row["SubOrder1"] = excel_row["SubOrder1"].ToString().Trim();
                        excel_row["SubOrder2"] = excel_row["SubOrder2"].ToString().Trim();
                        excel_row["SubOrder3"] = excel_row["SubOrder3"].ToString().Trim();
                    }
                    excel_dataReader.Close(); */
                    excelConnection.Close();
                    have_data = true;
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                    MessageBox.Show(ex.Message);
                }
            }

            if (have_data)
            {

                DataSet f5plan = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable f5plan_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [231_F5Plan]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(f5plan, "231_F5Plan");
                    f5plan_local = f5plan.Tables["231_F5Plan"];

                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        DataRow[] current_row = f5plan_local.Select("OrderNo='" + excel_row["OrderNo"].ToString() + "'");
                        if (current_row.Length > 0)
                        {
                            foreach (DataRow local_row in current_row)
                            {
                                local_row.BeginEdit();
                                local_row["SubOrder1"] = excel_row["SubOrder1"].ToString().Trim();
                                local_row["SubOrder2"] = excel_row["SubOrder2"].ToString().Trim();
                                local_row["SubOrder3"] = excel_row["SubOrder3"].ToString().Trim();
                                local_row.EndEdit();
                            }
                        }                        
                    }


                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(f5plan, "231_F5Plan");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();                    

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.");

                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot connect to AS400");
                }
            }
        }

        private void buttonFinishedReport_Click(object sender, EventArgs e)
        {
            frm_CheckFinished_F5Plan checkFinishedForm = new frm_CheckFinished_F5Plan();
            checkFinishedForm.ShowDialog();
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                saveFDlg.Title = "Save As";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        F5Plan.OptionsPrint.PrintDetails = true;
                        F5Plan.OptionsPrint.ExpandAllDetails = true;
                        F5Plan.OptionsPrint.AutoWidth = true;                        
                        F5Plan.ExportToXlsx(saveFDlg.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }
    }
}
